package org.common.convert.tohtml;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Base64;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class XlsxToHtml1 {

    public static void main(String[] args) {
        try (FileInputStream fis = new FileInputStream("D:\\temp\\中交（厦门）电子商务有限公司报价单（肯尼亚）.xlsx")) {
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表


            StringBuilder htmlContent = new StringBuilder();
            htmlContent.append("<html><head><style>table {border-collapse: collapse;} td, th {border: 1px solid black; padding: 5px;}</style></head><body>");

                  // 处理表格数据
              for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                   Row row = sheet.getRow(rowIndex);
                   htmlContent.append("<tr>");
                   for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                           Cell cell = row.getCell(colIndex);
                           if(cell!=null) {
                               htmlContent.append("<td>").append(getCellValue(cell)).append("</td>");
                           }else{
                               htmlContent.append("<td></td>");
                           }
                       }
                   htmlContent.append("</tr>");
               }
            // 获取工作表的 DrawingPatriarch
            Drawing<?> drawing = sheet.getDrawingPatriarch();
            if (drawing != null) {
                List<XSSFShape> shapes = ((XSSFDrawing) drawing).getShapes();
                for (XSSFShape shape : shapes) {
                    if (shape instanceof Picture) {
                        Picture picture = (Picture) shape;
                        PictureData pictureData = picture.getPictureData();
                        ClientAnchor cellRange = picture.getPreferredSize();
                        int firstCol = cellRange.getCol1();
                        int firstRow = cellRange.getRow1();
                        int lastCol = cellRange.getCol2();
                        int lastRow = cellRange.getRow2();
                        byte[] bytes = pictureData.getData();
                        String base64Image = Base64.getEncoder().encodeToString(bytes);
//                        htmlContent.append("<img src=\"data:image/").append(pictureData.getMimeType()).append(";base64,").append(base64Image).append("\" />");
                        htmlContent.append("<tr><td colspan='").append(lastCol - firstCol + 1).append("' rowspan='")
                                      .append(lastRow - firstRow + 1).append("'><img src='data:")
                                      .append(pictureData.getMimeType()).append(";base64,").append(base64Image)
                                      .append("' /></td></tr>");
                    } else if (shape instanceof XSSFShapeGroup) {
                        // 如果是形状组，递归遍历形状组内的形状
                        XSSFShapeGroup shapeGroup = (XSSFShapeGroup) shape;
                       htmlContent.append(convertShapeGroupToHTML(shapeGroup));
                    }
                }
            }


            htmlContent.append("</body></html>");

            // 将 HTML 内容写入文件
            File htmlFile = new File("D:\\temp\\中交（厦门）电子商务有限公司报价单（肯尼亚）.html");
            FileOutputStream fos = new FileOutputStream(htmlFile);
            fos.write(htmlContent.toString().getBytes());
            fos.close();

            System.out.println("HTML file created successfully.");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String convertShapeGroupToHTML(XSSFShapeGroup shapeGroup) {
        StringBuilder htmlBuilder = new StringBuilder("<div style='position: relative;'>");
        Iterator<XSSFShape> ites = shapeGroup.iterator();
        while (ites.hasNext()){
            XSSFShape shape = ites.next();
            if (shape instanceof XSSFPicture) {
                XSSFPicture picture = (XSSFPicture) shape;
                byte[] bytes = picture.getPictureData().getData();
                String base64Image = Base64.getEncoder().encodeToString(bytes);
                htmlBuilder.append("<img src=\"data:image/").append(picture.getPictureData().getMimeType()).append(";base64,").append(base64Image).append("\" />");

            } else if (shape instanceof XSSFTextBox) {
                htmlBuilder.append(convertTextBoxToHTML((XSSFTextBox) shape));
            }
        }

        htmlBuilder.append("</div>");
        return htmlBuilder.toString();
    }
    private static String convertImageToBase64(XSSFPicture picture) {
        byte[] bytes = picture.getPictureData().getData();
        return Base64.getEncoder().encodeToString(bytes);
    }
    private static String convertTextBoxToHTML(XSSFTextBox textBox) {
        XSSFAnchor anchor = textBox.getAnchor();
        int x1 = anchor.getDx1();
        int y1 = anchor.getDy1();
        int x2 = anchor.getDx2();
        int y2 = anchor.getDy2();

        String text = textBox.getText();
        String textContent = text;

        StringBuilder htmlBuilder = new StringBuilder();
        htmlBuilder.append("<div style='position: absolute; left: ")
                .append(x1)
                .append("px; top: ")
                .append(y1)
                .append("px; width: ")
                .append(x2 - x1)
                .append("px; height: ")
                .append(y2 - y1)
                .append("px;'>")
                .append(textContent)
                .append("</div>");

        return htmlBuilder.toString();
    }

    /**
     * 获取表格单元格Cell内容
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String result = new String();
        if (cell.getCellType() == CellType.NUMERIC) { // 数字类型
            if (DateUtil.isCellDateFormatted(cell)) { // 处理日期格式、时间格式
                SimpleDateFormat sdf = null;
                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                    sdf = new SimpleDateFormat("HH:mm");
                } else { // 日期
                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                }
                Date date = cell.getDateCellValue();
                result = sdf.format(date);
            } else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                double value = cell.getNumericCellValue();
                Date date = org.apache.poi.ss.usermodel.DateUtil
                        .getJavaDate(value);
                result = sdf.format(date);
            } else {
                double value = cell.getNumericCellValue();
                CellStyle style = cell.getCellStyle();
                DecimalFormat format = new DecimalFormat();
                String temp = style.getDataFormatString();
// 单元格设置成常规
                if (temp.equals("General")) {
                    format.applyPattern("#");
                }
                result = format.format(value);
            }

        } else if (cell.getCellType() == CellType.BLANK) {
            result = "";

        } else if (cell.getCellType() == CellType.ERROR) {
            result = String.valueOf(cell.getErrorCellValue());

        } else if (cell.getCellType() == CellType.FORMULA) {
            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
            CellValue cellValue = evaluator.evaluate(cell);
            System.out.println("公式计算结果: " + cellValue.getNumberValue());

        } else if (cell.getCellType() == CellType.BOOLEAN) {
            result = String.valueOf(cell.getBooleanCellValue());
        }
//            case CellType.DATE_FORMULA: //如果是公式
//                try{
//
//                        double nValue = cell.getNumericCellValue();
//                        String strFormat = "yyyy-MM-dd HH:mm:ss";
//                        if (nValue < 1) {
//                            strFormat = "HH:mm:ss";
//                        } else {
//                            if ( nValue - Math.floor(nValue) < 1.0/(24*60*60)) {
//                                // 少于1秒，表示只有日期
//                                strFormat = "yyyy-MM-dd";
//                            }
//                        }
//                        Date theDate = cell.getDateCellValue();
//                        SimpleDateFormat dff = new SimpleDateFormat(strFormat);
//                        result = dff.format(theDate);
//
//                }catch(Exception e){
//                    result = "";
//                    e.printStackTrace();
//                }
//                break;
        else {
            if (cell.getRichStringCellValue() != null) {
                result = cell.getRichStringCellValue().toString();
            } else {
                result = "";
            }
        }

        return result;
    }
}
